Jassif Team Institution

let mysql = require('mysql');

let con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  let sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result.affectedRows + " record(s) updated");
  });
});

C:\Users\Your Name>node demo_db_update.js

1 record(s) updated

{
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '(Rows matched: 1 Changed: 1 Warnings: 0',
  protocol41: true,
  changedRows: 1
}

console.log(result.affectedRows)

1

// Update single record by ID
let id = 5;
let newAddress = 'New Address 123';
let sql = "UPDATE customers SET address = ? WHERE id = ?";
con.query(sql, [newAddress, id], function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
  console.log("Records changed: " + result.changedRows);
});

// Update multiple records
let oldCity = 'Old City';
let newCity = 'New City';
let sql = "UPDATE customers SET city = ? WHERE city = ?";
con.query(sql, [newCity, oldCity], function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
});

// Update with IN operator
let ids = [1, 3, 7];
let status = 'active';
let sql = "UPDATE customers SET status = ? WHERE id IN (?)";
con.query(sql, [status, ids], function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
});

// Update multiple columns
let id = 5;
let newName = 'John Updated';
let newAddress = 'Updated Address 456';
let newEmail = 'john.updated@email.com';

let sql = "UPDATE customers SET name = ?, address = ?, email = ? WHERE id = ?";
con.query(sql, [newName, newAddress, newEmail, id], function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
});

// Update with calculated values
let sql = "UPDATE products SET price = price * 1.1 WHERE category = 'electronics'";
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
});

// Update with expressions
let sql = "UPDATE orders SET total_amount = quantity * unit_price WHERE status = 'pending'";
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
});

// Update using subquery
let sql = `
  UPDATE customers 
  SET premium = 1 
  WHERE id IN (
    SELECT customer_id 
    FROM orders 
    WHERE total_amount > 1000
  )
`;
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
});

// Update with CASE statement
let sql = `
  UPDATE products 
  SET discount = 
    CASE 
      WHEN price > 100 THEN 0.2 
      WHEN price > 50 THEN 0.1 
      ELSE 0 
    END
`;
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
});

// First check what will be updated
let id = 5;
con.query("SELECT * FROM customers WHERE id = ?", [id], function (err, result) {
  if (err) throw err;
  
  if (result.length > 0) {
    console.log("Will update:", result[0]);
    
    // Then proceed with update
    let newAddress = 'Updated Address';
    con.query("UPDATE customers SET address = ? WHERE id = ?", [newAddress, id], function (err, updateResult) {
      if (err) throw err;
      console.log("Records updated: " + updateResult.affectedRows);
    });
  } else {
    console.log("No records found to update");
  }
});

// Using transactions for safe update
con.beginTransaction(function(err) {
  if (err) throw err;
  
  let sql = "UPDATE customers SET address = ? WHERE id = ?";
  con.query(sql, ['New Address', 5], function (err, result) {
    if (err) {
      return con.rollback(function() {
        throw err;
      });
    }
    
    console.log("Records updated: " + result.affectedRows);
    
    // Commit the transaction
    con.commit(function(err) {
      if (err) {
        return con.rollback(function() {
          throw err;
        });
      }
      console.log("Transaction completed successfully");
    });
  });
});

// Update with LIMIT for safety
let sql = "UPDATE customers SET status = 'inactive' WHERE last_login < '2023-01-01' LIMIT 10";
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Records updated: " + result.affectedRows);
});

const mysql = require('mysql');

// Create connection
const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "company_db"
});

// Connect to MySQL
con.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  
  console.log('Connected to MySQL database');
  
  // Example 1: Basic UPDATE with WHERE
  console.log('\n1. Basic UPDATE with WHERE:');
  const oldAddress = 'Valley 345';
  const newAddress = 'Canyon 123';
  con.query("UPDATE customers SET address = ? WHERE address = ?", [newAddress, oldAddress], (err, result) => {
    if (err) {
      console.error('Error in update 1:', err.message);
      return;
    }
    console.log(`Records affected: ${result.affectedRows}`);
    console.log(`Records changed: ${result.changedRows}`);
    console.log('Full result:', result);
  });
  
  // Example 2: UPDATE multiple columns
  console.log('\n2. UPDATE multiple columns:');
  const customerId = 5;
  const updates = {
    name: 'John Smith Updated',
    email: 'john.updated@email.com',
    phone: '+1234567890'
  };
  
  con.query(
    "UPDATE customers SET name = ?, email = ?, phone = ? WHERE id = ?",
    [updates.name, updates.email, updates.phone, customerId],
    (err, result) => {
      if (err) {
        console.error('Error in update 2:', err.message);
        return;
      }
      console.log(`Records affected: ${result.affectedRows}`);
    }
  );
  
  // Example 3: Safe UPDATE with transaction
  console.log('\n3. Safe UPDATE with transaction:');
  con.beginTransaction((err) => {
    if (err) {
      console.error('Error starting transaction:', err.message);
      return;
    }
    
    // First check what we're updating
    const updateId = 3;
    con.query("SELECT * FROM customers WHERE id = ?", [updateId], (err, selectResult) => {
      if (err) {
        return con.rollback(() => {
          console.error('Error in select:', err.message);
        });
      }
      
      if (selectResult.length > 0) {
        console.log('Will update customer:', selectResult[0]);
        
        // Perform update
        const newStatus = 'premium';
        con.query("UPDATE customers SET status = ? WHERE id = ?", [newStatus, updateId], (err, updateResult) => {
          if (err) {
            return con.rollback(() => {
              console.error('Error in update:', err.message);
            });
          }
          
          console.log(`Transaction - Records affected: ${updateResult.affectedRows}`);
          
          // Commit transaction
          con.commit((err) => {
            if (err) {
              return con.rollback(() => {
                console.error('Error committing transaction:', err.message);
              });
            }
            console.log('Transaction committed successfully');
            
            // Close connection
            setTimeout(() => {
              con.end((err) => {
                if (err) {
                  console.error('Error closing connection:', err.message);
                  return;
                }
                console.log('\nConnection closed');
              });
            }, 1000);
          });
        });
      } else {
        console.log('No customer found with ID:', updateId);
        con.rollback(() => {
          console.log('Transaction rolled back');
        });
      }
    });
  });
});